import sqlite3

class DbManager(object):

    def __init__(self, *args):
        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, types, value, traceback):
        self.db.commit()
        return False

    def __del__(self):
        self.db.commit()
        self.db.close()

    def switchDb(self, *args):
        self.db.close()

        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def createTable(self, tableString):
        self.cursor.execute(tableString)
        self.db.commit()

    def commitAndClose(self):
        self.db.commit()
        self.db.close()

class SQLiteDb(DbManager):

    yueUsers = """
    CREATE TABLE IF NOT EXISTS yueUsers(
        id INTEGER PRIMARY KEY autoincrement,
        city TEXT,
        nick TEXT,
        sevicecount TEXT,
        gender TEXT,
        types TEXT,
        tm TEXT
    )"""

    def __init__(self, database='', *args):
        super().__init__(database, *args)
        self.initDb()

    def initDb(self):
        self.createTable(self.yueUsers)

    def addUsers(self, city, nick, sevicecount, gender, types, tm):
        try:
            insertData = self.cursor.execute("""INSERT INTO yueUsers 
                (city, nick, sevicecount, gender, types, tm) VALUES 
                ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')
                """.format(city, nick, sevicecount, gender, types, tm))
        except:
            pass

        self.db.commit()

    def delUsers(self, uid):
        self.cursor.execute("""DELETE FROM yueUsers WHERE id={}""".format(uid))
        self.db.commit()

    def fetchUsers(self, limit = 50, page = 1):
        self.cursor.execute("""SELECT * FROM yueUsers 
            ORDER BY id DESC LIMIT {0} OFFSET {1}""".format(limit, (page - 1)*limit))
        return self.cursor.fetchall()
